package org.lc.scoretj.controller;

import org.lc.scoretj.dto.ApiResponse;
import org.lc.scoretj.dto.StatisticsQueryDTO;
import org.lc.scoretj.model.*;
import org.lc.scoretj.repository.*;
import org.lc.scoretj.service.ScoreStatisticsService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;

import jakarta.servlet.ServletOutputStream;
import jakarta.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.bind.annotation.RequestPart;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 统计控制器
 */
@RestController
@RequestMapping("/api/statistics")
@CrossOrigin(origins = "*")
public class StatisticsController {

    private static final Logger logger = LoggerFactory.getLogger(StatisticsController.class);

    @Autowired
    private ScoreStatisticsService scoreStatisticsService;

    @Autowired
    private TrainingLevelStatisticsRepository trainingLevelStatisticsRepository;

    @Autowired
    private TrainingLevelMajorStatisticsRepository trainingLevelMajorStatisticsRepository;

    @Autowired
    private TrainingLevelMajorTeamStatisticsRepository trainingLevelMajorTeamStatisticsRepository;

    @Autowired
    private TrainingLevelMajorTeamCourseStatisticsRepository trainingLevelMajorTeamCourseStatisticsRepository;

    @Autowired
    private CourseStatisticsRepository courseStatisticsRepository;

    @Autowired
    private CourseGapAnalysisRepository courseGapAnalysisRepository;

    @Autowired
    private ScoreRepository scoreRepository;


    /**
     * 按培养层次统计
     */
    @GetMapping("/training-level")
    public ResponseEntity<ApiResponse<List<TrainingLevelStatistics>>> getTrainingLevelStatistics(
            @RequestParam(required = false) String xueQe,
            @RequestParam(defaultValue = "1") int page,
            @RequestParam(defaultValue = "10") int size,
            @RequestParam(defaultValue = "id") String sortBy,
            @RequestParam(defaultValue = "asc") String sortOrder) {
        
        Pageable pageable = PageRequest.of(page - 1, size, Sort.by(Sort.Direction.fromString(sortOrder), sortBy));
        Page<TrainingLevelStatistics> result;
        
        if (xueQe != null && !xueQe.isEmpty()) {
            result = trainingLevelStatisticsRepository.findByXueQe(xueQe, pageable);
        } else {
            result = trainingLevelStatisticsRepository.findAll(pageable);
        }
        
        return ResponseEntity.ok(ApiResponse.success(result.getContent()));
    }

    /**
     * 按培养层次-专业统计
     */
    @GetMapping("/training-level-major")
    public ResponseEntity<ApiResponse<List<TrainingLevelMajorStatistics>>> getTrainingLevelMajorStatistics(
            @RequestParam(required = false) String xueQe,
            @RequestParam(required = false) String peiYangCengCi,
            @RequestParam(defaultValue = "1") int page,
            @RequestParam(defaultValue = "10") int size,
            @RequestParam(defaultValue = "id") String sortBy,
            @RequestParam(defaultValue = "asc") String sortOrder) {
        
        Pageable pageable = PageRequest.of(page - 1, size, Sort.by(Sort.Direction.fromString(sortOrder), sortBy));
        Page<TrainingLevelMajorStatistics> result;
        
        if (xueQe != null && !xueQe.isEmpty() && peiYangCengCi != null && !peiYangCengCi.isEmpty()) {
            result = trainingLevelMajorStatisticsRepository.findByXueQeAndPeiYangCengCi(xueQe, peiYangCengCi, pageable);
        } else if (xueQe != null && !xueQe.isEmpty()) {
            result = trainingLevelMajorStatisticsRepository.findByXueQe(xueQe, pageable);
        } else {
            result = trainingLevelMajorStatisticsRepository.findAll(pageable);
        }
        
        return ResponseEntity.ok(ApiResponse.success(result.getContent()));
    }

    /**
     * 按培养层次-专业-队别统计
     */
    @GetMapping("/training-level-major-team")
    public ResponseEntity<ApiResponse<List<TrainingLevelMajorTeamStatistics>>> getTrainingLevelMajorTeamStatistics(
            @RequestParam(required = false) String xueQe,
            @RequestParam(required = false) String peiYangCengCi,
            @RequestParam(required = false) String zhuanYe,
            @RequestParam(defaultValue = "1") int page,
            @RequestParam(defaultValue = "10") int size,
            @RequestParam(defaultValue = "id") String sortBy,
            @RequestParam(defaultValue = "asc") String sortOrder) {
        
        Pageable pageable = PageRequest.of(page - 1, size, Sort.by(Sort.Direction.fromString(sortOrder), sortBy));
        Page<TrainingLevelMajorTeamStatistics> result;
        
        if (xueQe != null && !xueQe.isEmpty() && peiYangCengCi != null && !peiYangCengCi.isEmpty() && zhuanYe != null && !zhuanYe.isEmpty()) {
            result = trainingLevelMajorTeamStatisticsRepository.findByXueQeAndPeiYangCengCiAndZhuanYe(xueQe, peiYangCengCi, zhuanYe, pageable);
        } else if (xueQe != null && !xueQe.isEmpty()) {
            result = trainingLevelMajorTeamStatisticsRepository.findByXueQe(xueQe, pageable);
        } else {
            result = trainingLevelMajorTeamStatisticsRepository.findAll(pageable);
        }
        
        return ResponseEntity.ok(ApiResponse.success(result.getContent()));
    }

    /**
     * 按培养层次-专业-队别-课程统计
     */
    @GetMapping("/training-level-major-team-course")
    public ResponseEntity<ApiResponse<List<TrainingLevelMajorTeamCourseStatistics>>> getTrainingLevelMajorTeamCourseStatistics(
            @RequestParam(required = false) String xueQe,
            @RequestParam(required = false) String peiYangCengCi,
            @RequestParam(required = false) String zhuanYe,
            @RequestParam(required = false) String duiBie,
            @RequestParam(defaultValue = "1") int page,
            @RequestParam(defaultValue = "10") int size,
            @RequestParam(defaultValue = "id") String sortBy,
            @RequestParam(defaultValue = "asc") String sortOrder) {
        
        Pageable pageable = PageRequest.of(page - 1, size, Sort.by(Sort.Direction.fromString(sortOrder), sortBy));
        Page<TrainingLevelMajorTeamCourseStatistics> result;
        
        if (xueQe != null && !xueQe.isEmpty()) {
            result = trainingLevelMajorTeamCourseStatisticsRepository.findByXueQe(xueQe, pageable);
        } else {
            result = trainingLevelMajorTeamCourseStatisticsRepository.findAll(pageable);
        }
        
        return ResponseEntity.ok(ApiResponse.success(result.getContent()));
    }

    /**
     * 按课程统计
     */
    @GetMapping("/course")
    public ResponseEntity<ApiResponse<List<CourseStatistics>>> getCourseStatistics(
            @RequestParam(required = false) String xueQe,
            @RequestParam(required = false) String courseCode,
            @RequestParam(defaultValue = "1") int page,
            @RequestParam(defaultValue = "10") int size,
            @RequestParam(defaultValue = "id") String sortBy,
            @RequestParam(defaultValue = "asc") String sortOrder) {
        
        Pageable pageable = PageRequest.of(page - 1, size, Sort.by(Sort.Direction.fromString(sortOrder), sortBy));
        Page<CourseStatistics> result;
        
        if (xueQe != null && !xueQe.isEmpty() && courseCode != null && !courseCode.isEmpty()) {
            result = courseStatisticsRepository.findByXueQeAndCourseCode(xueQe, courseCode, pageable);
        } else if (xueQe != null && !xueQe.isEmpty()) {
            result = courseStatisticsRepository.findByXueQe(xueQe, pageable);
        } else {
            result = courseStatisticsRepository.findAll(pageable);
        }
        
        return ResponseEntity.ok(ApiResponse.success(result.getContent()));
    }

    /**
     * 课程间成绩差距分析
     */
    @GetMapping("/course-gap")
    public ResponseEntity<ApiResponse<List<CourseGapAnalysis>>> getCourseGapAnalysis(
            @RequestParam(required = false) String xueQe,
            @RequestParam(defaultValue = "1") int page,
            @RequestParam(defaultValue = "10") int size,
            @RequestParam(defaultValue = "id") String sortBy,
            @RequestParam(defaultValue = "asc") String sortOrder) {
        
        Pageable pageable = PageRequest.of(page - 1, size, Sort.by(Sort.Direction.fromString(sortOrder), sortBy));
        Page<CourseGapAnalysis> result;
        
        if (xueQe != null && !xueQe.isEmpty()) {
            result = courseGapAnalysisRepository.findByXueQe(xueQe, pageable);
        } else {
            result = courseGapAnalysisRepository.findAll(pageable);
        }
        
        return ResponseEntity.ok(ApiResponse.success(result.getContent()));
    }

    /**
     * 课程考核相关性分析
     */
    

    /**
     * 执行所有统计分析
     */
    @PostMapping("/execute-all")
    public ResponseEntity<ApiResponse<String>> executeAllStatistics(@RequestBody StatisticsQueryDTO query) {
        if (query.getXueQe() == null || query.getXueQe().isEmpty()) {
            return ResponseEntity.badRequest().body(ApiResponse.error("学期参数不能为空"));
        }
        
        try {
            scoreStatisticsService.executeAllStatistics(query.getXueQe());
            return ResponseEntity.ok(ApiResponse.success("统计分析执行成功"));
        } catch (Exception e) {
            return ResponseEntity.internalServerError().body(ApiResponse.error("统计分析执行失败: " + e.getMessage()));
        }
    }

    /**
     * 执行按培养层次统计
     */
    @PostMapping("/execute/training-level")
    public ResponseEntity<ApiResponse<String>> executeTrainingLevelStatistics(@RequestBody StatisticsQueryDTO query) {
        if (query.getXueQe() == null || query.getXueQe().isEmpty()) {
            return ResponseEntity.badRequest().body(ApiResponse.error("学期参数不能为空"));
        }
        
        try {
            scoreStatisticsService.statisticsByTrainingLevel(query.getXueQe());
            return ResponseEntity.ok(ApiResponse.success("按培养层次统计执行成功"));
        } catch (Exception e) {
            return ResponseEntity.internalServerError().body(ApiResponse.error("按培养层次统计执行失败: " + e.getMessage()));
        }
    }

    /**
     * 执行按培养层次-专业统计
     */
    @PostMapping("/execute/training-level-major")
    public ResponseEntity<ApiResponse<String>> executeTrainingLevelMajorStatistics(@RequestBody StatisticsQueryDTO query) {
        if (query.getXueQe() == null || query.getXueQe().isEmpty()) {
            return ResponseEntity.badRequest().body(ApiResponse.error("学期参数不能为空"));
        }
        
        try {
            scoreStatisticsService.statisticsByTrainingLevelAndMajor(query.getXueQe());
            return ResponseEntity.ok(ApiResponse.success("按培养层次-专业统计执行成功"));
        } catch (Exception e) {
            return ResponseEntity.internalServerError().body(ApiResponse.error("按培养层次-专业统计执行失败: " + e.getMessage()));
        }
    }

    /**
     * 执行按培养层次-专业-队别统计
     */
    @PostMapping("/execute/training-level-major-team")
    public ResponseEntity<ApiResponse<String>> executeTrainingLevelMajorTeamStatistics(@RequestBody StatisticsQueryDTO query) {
        if (query.getXueQe() == null || query.getXueQe().isEmpty()) {
            return ResponseEntity.badRequest().body(ApiResponse.error("学期参数不能为空"));
        }
        
        try {
            scoreStatisticsService.statisticsByTrainingLevelAndMajorAndTeam(query.getXueQe());
            return ResponseEntity.ok(ApiResponse.success("按培养层次-专业-队别统计执行成功"));
        } catch (Exception e) {
            return ResponseEntity.internalServerError().body(ApiResponse.error("按培养层次-专业-队别统计执行失败: " + e.getMessage()));
        }
    }

    /**
     * 执行按培养层次-专业-队别-课程统计
     */
    @PostMapping("/execute/training-level-major-team-course")
    public ResponseEntity<ApiResponse<String>> executeTrainingLevelMajorTeamCourseStatistics(@RequestBody StatisticsQueryDTO query) {
        if (query.getXueQe() == null || query.getXueQe().isEmpty()) {
            return ResponseEntity.badRequest().body(ApiResponse.error("学期参数不能为空"));
        }
        
        try {
            scoreStatisticsService.statisticsByTrainingLevelAndMajorAndTeamAndCourse(query.getXueQe());
            return ResponseEntity.ok(ApiResponse.success("按培养层次-专业-队别-课程统计执行成功"));
        } catch (Exception e) {
            return ResponseEntity.internalServerError().body(ApiResponse.error("按培养层次-专业-队别-课程统计执行失败: " + e.getMessage()));
        }
    }

    /**
     * 执行按课程统计
     */
    @PostMapping("/execute/course")
    public ResponseEntity<ApiResponse<String>> executeCourseStatistics(@RequestBody StatisticsQueryDTO query) {
        if (query.getXueQe() == null || query.getXueQe().isEmpty()) {
            return ResponseEntity.badRequest().body(ApiResponse.error("学期参数不能为空"));
        }
        
        try {
            scoreStatisticsService.statisticsByCourse(query.getXueQe());
            return ResponseEntity.ok(ApiResponse.success("按课程统计执行成功"));
        } catch (Exception e) {
            return ResponseEntity.internalServerError().body(ApiResponse.error("按课程统计执行失败: " + e.getMessage()));
        }
    }

    /**
     * 执行课程间成绩差距分析
     */
    @PostMapping("/execute/course-gap")
    public ResponseEntity<ApiResponse<String>> executeCourseGapAnalysis(@RequestBody StatisticsQueryDTO query) {
        if (query.getXueQe() == null || query.getXueQe().isEmpty()) {
            return ResponseEntity.badRequest().body(ApiResponse.error("学期参数不能为空"));
        }
        
        try {
            scoreStatisticsService.analyzeCourseGap(query.getXueQe());
            return ResponseEntity.ok(ApiResponse.success("课程间成绩差距分析执行成功"));
        } catch (Exception e) {
            return ResponseEntity.internalServerError().body(ApiResponse.error("课程间成绩差距分析执行失败: " + e.getMessage()));
        }
    }

    /**
     * 执行课程考核相关性分析
     */
    

    /**
     * 清空所有统计数据
     */
    @PostMapping("/clear-all")
    public ResponseEntity<ApiResponse<String>> clearAllStatistics() {
        try {
            scoreStatisticsService.clearAllStatistics();
            return ResponseEntity.ok(ApiResponse.success("清空统计数据成功"));
        } catch (Exception e) {
            return ResponseEntity.internalServerError().body(ApiResponse.error("清空统计数据失败: " + e.getMessage()));
        }
    }

    /**
     * 导出统计数据为 Excel 文件（包含部分分析表）
     */
    @GetMapping("/export")
    public void exportStatisticsExcel(@RequestParam(required = false) String xueQe, HttpServletResponse response) {
    String fileName = null;
    try (XSSFWorkbook workbook = new XSSFWorkbook()) {
            // 1) Training Level sheet
            Sheet tlSheet = workbook.createSheet("training-level");
            Row tlHeader = tlSheet.createRow(0);
        String[] tlCols = new String[]{"学期","培养层次","总人数",
            "形成性平均分","形成性中位数","终结性平均分","终结性中位数","总评平均分","总评中位数",
            "形成性标准差","终结性标准差","总评标准差",
            "形成性-优秀","形成性-优秀率","形成性-良好","形成性-良好率","形成性-及格","形成性-及格率","形成性-不及格","形成性-不及格率",
            "终结性-优秀","终结性-优秀率","终结性-良好","终结性-良好率","终结性-及格","终结性-及格率","终结性-不及格","终结性-不及格率",
            "总评-优秀","总评-优秀率","总评-良好","总评-良好率","总评-及格","总评-及格率","总评-不及格","总评-不及格率"};
            for (int i = 0; i < tlCols.length; i++) tlHeader.createCell(i).setCellValue(tlCols[i]);

            List<org.lc.scoretj.model.TrainingLevelStatistics> tlStats = xueQe != null && !xueQe.isEmpty()
                    ? trainingLevelStatisticsRepository.findByXueQe(xueQe, org.springframework.data.domain.Pageable.unpaged()).getContent()
                    : trainingLevelStatisticsRepository.findAll();

            int tlRowIdx = 1;
            for (org.lc.scoretj.model.TrainingLevelStatistics s : tlStats) {
                Row r = tlSheet.createRow(tlRowIdx++);
                r.createCell(0).setCellValue(s.getXueQe() == null ? "" : s.getXueQe());
                r.createCell(1).setCellValue(s.getPeiYangCengCi() == null ? "" : s.getPeiYangCengCi());
                r.createCell(2).setCellValue(s.getTotalCount() == null ? 0 : s.getTotalCount());
                r.createCell(3).setCellValue(s.getXcxAverage() == null ? 0.0 : s.getXcxAverage());
                r.createCell(4).setCellValue(s.getXcxMedian() == null ? 0.0 : s.getXcxMedian());
                r.createCell(5).setCellValue(s.getZjxAverage() == null ? 0.0 : s.getZjxAverage());
                r.createCell(6).setCellValue(s.getZjxMedian() == null ? 0.0 : s.getZjxMedian());
                r.createCell(7).setCellValue(s.getZpAverage() == null ? 0.0 : s.getZpAverage());
                r.createCell(8).setCellValue(s.getZpMedian() == null ? 0.0 : s.getZpMedian());
                r.createCell(9).setCellValue(s.getXcxStandardDeviation() == null ? 0.0 : s.getXcxStandardDeviation());
                r.createCell(10).setCellValue(s.getZjxStandardDeviation() == null ? 0.0 : s.getZjxStandardDeviation());
                r.createCell(11).setCellValue(s.getZpStandardDeviation() == null ? 0.0 : s.getZpStandardDeviation());
                int colIdx = 12;
                // 输出 counts 和 rate（直接使用实体中的 rate 字段，保留小数精度）
                int total = s.getTotalCount() == null ? 0 : s.getTotalCount();
                int[] counts = new int[]{s.getXcxExcellentCount()==null?0:s.getXcxExcellentCount(), s.getXcxGoodCount()==null?0:s.getXcxGoodCount(), s.getXcxPassCount()==null?0:s.getXcxPassCount(), s.getXcxFailCount()==null?0:s.getXcxFailCount()};
                Float[] rates = new Float[]{s.getXcxExcellentRate(), s.getXcxGoodRate(), s.getXcxPassRate(), s.getXcxFailRate()};
                for (int k = 0; k < counts.length; k++) {
                    r.createCell(colIdx++).setCellValue(counts[k]);
                    r.createCell(colIdx++).setCellValue(rates[k] == null ? 0.0 : rates[k]);
                }
                int[] zjxCounts = new int[]{s.getZjxExcellentCount()==null?0:s.getZjxExcellentCount(), s.getZjxGoodCount()==null?0:s.getZjxGoodCount(), s.getZjxPassCount()==null?0:s.getZjxPassCount(), s.getZjxFailCount()==null?0:s.getZjxFailCount()};
                Float[] zjxRates = new Float[]{s.getZjxExcellentRate(), s.getZjxGoodRate(), s.getZjxPassRate(), s.getZjxFailRate()};
                for (int k = 0; k < zjxCounts.length; k++) {
                    r.createCell(colIdx++).setCellValue(zjxCounts[k]);
                    r.createCell(colIdx++).setCellValue(zjxRates[k] == null ? 0.0 : zjxRates[k]);
                }
                int[] zpCounts = new int[]{s.getZpExcellentCount()==null?0:s.getZpExcellentCount(), s.getZpGoodCount()==null?0:s.getZpGoodCount(), s.getZpPassCount()==null?0:s.getZpPassCount(), s.getZpFailCount()==null?0:s.getZpFailCount()};
                Float[] zpRates = new Float[]{s.getZpExcellentRate(), s.getZpGoodRate(), s.getZpPassRate(), s.getZpFailRate()};
                for (int k = 0; k < zpCounts.length; k++) {
                    r.createCell(colIdx++).setCellValue(zpCounts[k]);
                    r.createCell(colIdx++).setCellValue(zpRates[k] == null ? 0.0 : zpRates[k]);
                }
                // fixed summary counts removed (counts/rates already written above)
            }

            // 2) Training Level - Major sheet
            Sheet tlmSheet = workbook.createSheet("training-level-major");
            Row tlmHeader = tlmSheet.createRow(0);
        String[] tlmCols = new String[]{"学期","培养层次","专业","总人数",
            "形成性平均分","形成性中位数","终结性平均分","终结性中位数","总评平均分","总评中位数",
            "形成性-优秀","形成性-优秀率","形成性-良好","形成性-良好率","形成性-及格","形成性-及格率","形成性-不及格","形成性-不及格率",
            "终结性-优秀","终结性-优秀率","终结性-良好","终结性-良好率","终结性-及格","终结性-及格率","终结性-不及格","终结性-不及格率",
            "总评-优秀","总评-优秀率","总评-良好","总评-良好率","总评-及格","总评-及格率","总评-不及格","总评-不及格率"};
            for (int i = 0; i < tlmCols.length; i++) tlmHeader.createCell(i).setCellValue(tlmCols[i]);

            List<org.lc.scoretj.model.TrainingLevelMajorStatistics> tlmStats = xueQe != null && !xueQe.isEmpty()
                    ? trainingLevelMajorStatisticsRepository.findByXueQe(xueQe, org.springframework.data.domain.Pageable.unpaged()).getContent()
                    : trainingLevelMajorStatisticsRepository.findAll();

            int tlmRowIdx = 1;
            for (org.lc.scoretj.model.TrainingLevelMajorStatistics s : tlmStats) {
                Row r = tlmSheet.createRow(tlmRowIdx++);
                r.createCell(0).setCellValue(s.getXueQe() == null ? "" : s.getXueQe());
                r.createCell(1).setCellValue(s.getPeiYangCengCi() == null ? "" : s.getPeiYangCengCi());
                r.createCell(2).setCellValue(s.getZhuanYe() == null ? "" : s.getZhuanYe());
                r.createCell(3).setCellValue(s.getTotalCount() == null ? 0 : s.getTotalCount());
                r.createCell(4).setCellValue(s.getXcxAverage() == null ? 0.0 : s.getXcxAverage());
                r.createCell(5).setCellValue(s.getXcxMedian() == null ? 0.0 : s.getXcxMedian());
                r.createCell(6).setCellValue(s.getZjxAverage() == null ? 0.0 : s.getZjxAverage());
                r.createCell(7).setCellValue(s.getZjxMedian() == null ? 0.0 : s.getZjxMedian());
                r.createCell(8).setCellValue(s.getZpAverage() == null ? 0.0 : s.getZpAverage());
                r.createCell(9).setCellValue(s.getZpMedian() == null ? 0.0 : s.getZpMedian());
                int colIdx2 = 10;
                int total2 = s.getTotalCount() == null ? 0 : s.getTotalCount();
                int[] counts2 = new int[]{s.getXcxExcellentCount()==null?0:s.getXcxExcellentCount(), s.getXcxGoodCount()==null?0:s.getXcxGoodCount(), s.getXcxPassCount()==null?0:s.getXcxPassCount(), s.getXcxFailCount()==null?0:s.getXcxFailCount()};
                Float[] rates2 = new Float[]{s.getXcxExcellentRate(), s.getXcxGoodRate(), s.getXcxPassRate(), s.getXcxFailRate()};
                for (int k = 0; k < counts2.length; k++) { r.createCell(colIdx2++).setCellValue(counts2[k]); r.createCell(colIdx2++).setCellValue(rates2[k] == null ? 0.0 : rates2[k]); }
                int[] zjxCounts2 = new int[]{s.getZjxExcellentCount()==null?0:s.getZjxExcellentCount(), s.getZjxGoodCount()==null?0:s.getZjxGoodCount(), s.getZjxPassCount()==null?0:s.getZjxPassCount(), s.getZjxFailCount()==null?0:s.getZjxFailCount()};
                Float[] zjxRates2 = new Float[]{s.getZjxExcellentRate(), s.getZjxGoodRate(), s.getZjxPassRate(), s.getZjxFailRate()};
                for (int k = 0; k < zjxCounts2.length; k++) { r.createCell(colIdx2++).setCellValue(zjxCounts2[k]); r.createCell(colIdx2++).setCellValue(zjxRates2[k] == null ? 0.0 : zjxRates2[k]); }
                int[] zpCounts2 = new int[]{s.getZpExcellentCount()==null?0:s.getZpExcellentCount(), s.getZpGoodCount()==null?0:s.getZpGoodCount(), s.getZpPassCount()==null?0:s.getZpPassCount(), s.getZpFailCount()==null?0:s.getZpFailCount()};
                Float[] zpRates2 = new Float[]{s.getZpExcellentRate(), s.getZpGoodRate(), s.getZpPassRate(), s.getZpFailRate()};
                for (int k = 0; k < zpCounts2.length; k++) { r.createCell(colIdx2++).setCellValue(zpCounts2[k]); r.createCell(colIdx2++).setCellValue(zpRates2[k] == null ? 0.0 : zpRates2[k]); }
            }

            // 3) Training Level - Major - Team sheet
            // sheet name kept under Excel 31-char limit
            Sheet tlmtSheet = workbook.createSheet("training-level-major-team");
            Row tlmtHeader = tlmtSheet.createRow(0);
        String[] tlmtCols = new String[]{"学期","培养层次","专业","队别","总人数",
            "形成性平均分","形成性中位数","终结性平均分","终结性中位数","总评平均分","总评中位数",
            "形成性-优秀","形成性-优秀率","形成性-良好","形成性-良好率","形成性-及格","形成性-及格率","形成性-不及格","形成性-不及格率",
            "终结性-优秀","终结性-优秀率","终结性-良好","终结性-良好率","终结性-及格","终结性-及格率","终结性-不及格","终结性-不及格率",
            "总评-优秀","总评-优秀率","总评-良好","总评-良好率","总评-及格","总评-及格率","总评-不及格","总评-不及格率"};
            for (int i = 0; i < tlmtCols.length; i++) tlmtHeader.createCell(i).setCellValue(tlmtCols[i]);

            List<org.lc.scoretj.model.TrainingLevelMajorTeamStatistics> tlmtStats = xueQe != null && !xueQe.isEmpty()
                    ? trainingLevelMajorTeamStatisticsRepository.findByXueQe(xueQe, org.springframework.data.domain.Pageable.unpaged()).getContent()
                    : trainingLevelMajorTeamStatisticsRepository.findAll();

            int tlmtRowIdx = 1;
            for (org.lc.scoretj.model.TrainingLevelMajorTeamStatistics s : tlmtStats) {
                Row r = tlmtSheet.createRow(tlmtRowIdx++);
                r.createCell(0).setCellValue(s.getXueQe() == null ? "" : s.getXueQe());
                r.createCell(1).setCellValue(s.getPeiYangCengCi() == null ? "" : s.getPeiYangCengCi());
                r.createCell(2).setCellValue(s.getZhuanYe() == null ? "" : s.getZhuanYe());
                r.createCell(3).setCellValue(s.getDuiBie() == null ? "" : s.getDuiBie());
                r.createCell(4).setCellValue(s.getTotalCount() == null ? 0 : s.getTotalCount());
                // averages and medians
                r.createCell(5).setCellValue(s.getXcxAverage() == null ? 0.0 : s.getXcxAverage());
                r.createCell(6).setCellValue(s.getXcxMedian() == null ? 0.0 : s.getXcxMedian());
                r.createCell(7).setCellValue(s.getZjxAverage() == null ? 0.0 : s.getZjxAverage());
                r.createCell(8).setCellValue(s.getZjxMedian() == null ? 0.0 : s.getZjxMedian());
                r.createCell(9).setCellValue(s.getZpAverage() == null ? 0.0 : s.getZpAverage());
                r.createCell(10).setCellValue(s.getZpMedian() == null ? 0.0 : s.getZpMedian());

                // counts and rates for 4 bands (优秀/良好/及格/不及格)
                // 形成性 (columns 11-18)
                r.createCell(11).setCellValue(s.getXcxExcellentCount() == null ? 0 : s.getXcxExcellentCount());
                r.createCell(12).setCellValue(s.getXcxExcellentRate() == null ? 0.0 : s.getXcxExcellentRate());
                r.createCell(13).setCellValue(s.getXcxGoodCount() == null ? 0 : s.getXcxGoodCount());
                r.createCell(14).setCellValue(s.getXcxGoodRate() == null ? 0.0 : s.getXcxGoodRate());
                r.createCell(15).setCellValue(s.getXcxPassCount() == null ? 0 : s.getXcxPassCount());
                r.createCell(16).setCellValue(s.getXcxPassRate() == null ? 0.0 : s.getXcxPassRate());
                r.createCell(17).setCellValue(s.getXcxFailCount() == null ? 0 : s.getXcxFailCount());
                r.createCell(18).setCellValue(s.getXcxFailRate() == null ? 0.0 : s.getXcxFailRate());

                // 终结性 (columns 19-26)
                r.createCell(19).setCellValue(s.getZjxExcellentCount() == null ? 0 : s.getZjxExcellentCount());
                r.createCell(20).setCellValue(s.getZjxExcellentRate() == null ? 0.0 : s.getZjxExcellentRate());
                r.createCell(21).setCellValue(s.getZjxGoodCount() == null ? 0 : s.getZjxGoodCount());
                r.createCell(22).setCellValue(s.getZjxGoodRate() == null ? 0.0 : s.getZjxGoodRate());
                r.createCell(23).setCellValue(s.getZjxPassCount() == null ? 0 : s.getZjxPassCount());
                r.createCell(24).setCellValue(s.getZjxPassRate() == null ? 0.0 : s.getZjxPassRate());
                r.createCell(25).setCellValue(s.getZjxFailCount() == null ? 0 : s.getZjxFailCount());
                r.createCell(26).setCellValue(s.getZjxFailRate() == null ? 0.0 : s.getZjxFailRate());

                // 总评 (columns 27-34)
                r.createCell(27).setCellValue(s.getZpExcellentCount() == null ? 0 : s.getZpExcellentCount());
                r.createCell(28).setCellValue(s.getZpExcellentRate() == null ? 0.0 : s.getZpExcellentRate());
                r.createCell(29).setCellValue(s.getZpGoodCount() == null ? 0 : s.getZpGoodCount());
                r.createCell(30).setCellValue(s.getZpGoodRate() == null ? 0.0 : s.getZpGoodRate());
                r.createCell(31).setCellValue(s.getZpPassCount() == null ? 0 : s.getZpPassCount());
                r.createCell(32).setCellValue(s.getZpPassRate() == null ? 0.0 : s.getZpPassRate());
                r.createCell(33).setCellValue(s.getZpFailCount() == null ? 0 : s.getZpFailCount());
                r.createCell(34).setCellValue(s.getZpFailRate() == null ? 0.0 : s.getZpFailRate());
            }

            // 4) Training Level - Major - Team - Course sheet
            Sheet tltmcSheet = workbook.createSheet("training-level-major-team-course");
            Row tltmcHeader = tltmcSheet.createRow(0);
        String[] tltmcCols = new String[]{"学期","培养层次","专业","队别","课程代码","课程名称","课程类型","总人数",
            "形成性平均分","形成性中位数","终结性平均分","终结性中位数","总评平均分","总评中位数",
            "形成性-优秀","形成性-优秀率","形成性-良好","形成性-良好率","形成性-及格","形成性-及格率","形成性-不及格","形成性-不及格率",
            "终结性-优秀","终结性-优秀率","终结性-良好","终结性-良好率","终结性-及格","终结性-及格率","终结性-不及格","终结性-不及格率",
            "总评-优秀","总评-优秀率","总评-良好","总评-良好率","总评-及格","总评-及格率","总评-不及格","总评-不及格率"};
            for (int i = 0; i < tltmcCols.length; i++) tltmcHeader.createCell(i).setCellValue(tltmcCols[i]);

            List<org.lc.scoretj.model.TrainingLevelMajorTeamCourseStatistics> tltmcStats = xueQe != null && !xueQe.isEmpty()
                    ? trainingLevelMajorTeamCourseStatisticsRepository.findByXueQe(xueQe, org.springframework.data.domain.Pageable.unpaged()).getContent()
                    : trainingLevelMajorTeamCourseStatisticsRepository.findAll();

            int tltmcRowIdx = 1;
            for (org.lc.scoretj.model.TrainingLevelMajorTeamCourseStatistics s : tltmcStats) {
                Row r = tltmcSheet.createRow(tltmcRowIdx++);
                r.createCell(0).setCellValue(s.getXueQe() == null ? "" : s.getXueQe());
                r.createCell(1).setCellValue(s.getPeiYangCengCi() == null ? "" : s.getPeiYangCengCi());
                r.createCell(2).setCellValue(s.getZhuanYe() == null ? "" : s.getZhuanYe());
                r.createCell(3).setCellValue(s.getDuiBie() == null ? "" : s.getDuiBie());
                r.createCell(4).setCellValue(s.getCourseCode() == null ? "" : s.getCourseCode());
                r.createCell(5).setCellValue(s.getCourseName() == null ? "" : s.getCourseName());
                r.createCell(6).setCellValue(s.getCourseType() == null ? "" : s.getCourseType());
                r.createCell(7).setCellValue(s.getTotalCount() == null ? 0 : s.getTotalCount());
                // averages and medians
                r.createCell(8).setCellValue(s.getXcxAverage() == null ? 0.0 : s.getXcxAverage());
                r.createCell(9).setCellValue(s.getXcxMedian() == null ? 0.0 : s.getXcxMedian());
                r.createCell(10).setCellValue(s.getZjxAverage() == null ? 0.0 : s.getZjxAverage());
                r.createCell(11).setCellValue(s.getZjxMedian() == null ? 0.0 : s.getZjxMedian());
                r.createCell(12).setCellValue(s.getZpAverage() == null ? 0.0 : s.getZpAverage());
                r.createCell(13).setCellValue(s.getZpMedian() == null ? 0.0 : s.getZpMedian());

                // 形成性 counts/rates (14-21)
                r.createCell(14).setCellValue(s.getXcxExcellentCount() == null ? 0 : s.getXcxExcellentCount());
                r.createCell(15).setCellValue(s.getXcxExcellentRate() == null ? 0.0 : s.getXcxExcellentRate());
                r.createCell(16).setCellValue(s.getXcxGoodCount() == null ? 0 : s.getXcxGoodCount());
                r.createCell(17).setCellValue(s.getXcxGoodRate() == null ? 0.0 : s.getXcxGoodRate());
                r.createCell(18).setCellValue(s.getXcxPassCount() == null ? 0 : s.getXcxPassCount());
                r.createCell(19).setCellValue(s.getXcxPassRate() == null ? 0.0 : s.getXcxPassRate());
                r.createCell(20).setCellValue(s.getXcxFailCount() == null ? 0 : s.getXcxFailCount());
                r.createCell(21).setCellValue(s.getXcxFailRate() == null ? 0.0 : s.getXcxFailRate());

                // 终结性 counts/rates (22-29)
                r.createCell(22).setCellValue(s.getZjxExcellentCount() == null ? 0 : s.getZjxExcellentCount());
                r.createCell(23).setCellValue(s.getZjxExcellentRate() == null ? 0.0 : s.getZjxExcellentRate());
                r.createCell(24).setCellValue(s.getZjxGoodCount() == null ? 0 : s.getZjxGoodCount());
                r.createCell(25).setCellValue(s.getZjxGoodRate() == null ? 0.0 : s.getZjxGoodRate());
                r.createCell(26).setCellValue(s.getZjxPassCount() == null ? 0 : s.getZjxPassCount());
                r.createCell(27).setCellValue(s.getZjxPassRate() == null ? 0.0 : s.getZjxPassRate());
                r.createCell(28).setCellValue(s.getZjxFailCount() == null ? 0 : s.getZjxFailCount());
                r.createCell(29).setCellValue(s.getZjxFailRate() == null ? 0.0 : s.getZjxFailRate());

                // 总评 counts/rates (30-37)
                r.createCell(30).setCellValue(s.getZpExcellentCount() == null ? 0 : s.getZpExcellentCount());
                r.createCell(31).setCellValue(s.getZpExcellentRate() == null ? 0.0 : s.getZpExcellentRate());
                r.createCell(32).setCellValue(s.getZpGoodCount() == null ? 0 : s.getZpGoodCount());
                r.createCell(33).setCellValue(s.getZpGoodRate() == null ? 0.0 : s.getZpGoodRate());
                r.createCell(34).setCellValue(s.getZpPassCount() == null ? 0 : s.getZpPassCount());
                r.createCell(35).setCellValue(s.getZpPassRate() == null ? 0.0 : s.getZpPassRate());
                r.createCell(36).setCellValue(s.getZpFailCount() == null ? 0 : s.getZpFailCount());
                r.createCell(37).setCellValue(s.getZpFailRate() == null ? 0.0 : s.getZpFailRate());
            }

            // 5) Course sheet
            Sheet cSheet = workbook.createSheet("course");
            Row cHeader = cSheet.createRow(0);
    String[] cCols = new String[]{"学期","课程代码","课程名称","课程类型","总人数",
            "形成性平均分","形成性中位数","终结性平均分","终结性中位数","总评平均分","总评中位数",
            "形成性-优秀","形成性-优秀率","形成性-良好","形成性-良好率","形成性-及格","形成性-及格率","形成性-不及格","形成性-不及格率",
            "终结性-优秀","终结性-优秀率","终结性-良好","终结性-良好率","终结性-及格","终结性-及格率","终结性-不及格","终结性-不及格率",
            "总评-优秀","总评-优秀率","总评-良好","总评-良好率","总评-及格","总评-及格率","总评-不及格","总评-不及格率"};
            for (int i = 0; i < cCols.length; i++) cHeader.createCell(i).setCellValue(cCols[i]);

            List<org.lc.scoretj.model.CourseStatistics> cStats = xueQe != null && !xueQe.isEmpty()
                    ? courseStatisticsRepository.findByXueQe(xueQe, org.springframework.data.domain.Pageable.unpaged()).getContent()
                    : courseStatisticsRepository.findAll();

            int cRowIdx = 1;
            for (org.lc.scoretj.model.CourseStatistics s : cStats) {
                Row r = cSheet.createRow(cRowIdx++);
                r.createCell(0).setCellValue(s.getXueQe() == null ? "" : s.getXueQe());
                r.createCell(1).setCellValue(s.getCourseCode() == null ? "" : s.getCourseCode());
                r.createCell(2).setCellValue(s.getCourseName() == null ? "" : s.getCourseName());
                r.createCell(3).setCellValue(s.getCourseType() == null ? "" : s.getCourseType());
                r.createCell(4).setCellValue(s.getTotalCount() == null ? 0 : s.getTotalCount());
                // Averages and medians
                r.createCell(5).setCellValue(s.getXcxAverage() == null ? 0.0 : s.getXcxAverage());
                r.createCell(6).setCellValue(s.getXcxMedian() == null ? 0.0 : s.getXcxMedian());
                r.createCell(7).setCellValue(s.getZjxAverage() == null ? 0.0 : s.getZjxAverage());
                r.createCell(8).setCellValue(s.getZjxMedian() == null ? 0.0 : s.getZjxMedian());
                r.createCell(9).setCellValue(s.getZpAverage() == null ? 0.0 : s.getZpAverage());
                r.createCell(10).setCellValue(s.getZpMedian() == null ? 0.0 : s.getZpMedian());

                // 形成性 counts/rates (11-18)
                r.createCell(11).setCellValue(s.getXcxExcellentCount() == null ? 0 : s.getXcxExcellentCount());
                r.createCell(12).setCellValue(s.getXcxExcellentRate() == null ? 0.0 : s.getXcxExcellentRate());
                r.createCell(13).setCellValue(s.getXcxGoodCount() == null ? 0 : s.getXcxGoodCount());
                r.createCell(14).setCellValue(s.getXcxGoodRate() == null ? 0.0 : s.getXcxGoodRate());
                r.createCell(15).setCellValue(s.getXcxPassCount() == null ? 0 : s.getXcxPassCount());
                r.createCell(16).setCellValue(s.getXcxPassRate() == null ? 0.0 : s.getXcxPassRate());
                r.createCell(17).setCellValue(s.getXcxFailCount() == null ? 0 : s.getXcxFailCount());
                r.createCell(18).setCellValue(s.getXcxFailRate() == null ? 0.0 : s.getXcxFailRate());

                // 终结性 counts/rates (19-26)
                r.createCell(19).setCellValue(s.getZjxExcellentCount() == null ? 0 : s.getZjxExcellentCount());
                r.createCell(20).setCellValue(s.getZjxExcellentRate() == null ? 0.0 : s.getZjxExcellentRate());
                r.createCell(21).setCellValue(s.getZjxGoodCount() == null ? 0 : s.getZjxGoodCount());
                r.createCell(22).setCellValue(s.getZjxGoodRate() == null ? 0.0 : s.getZjxGoodRate());
                r.createCell(23).setCellValue(s.getZjxPassCount() == null ? 0 : s.getZjxPassCount());
                r.createCell(24).setCellValue(s.getZjxPassRate() == null ? 0.0 : s.getZjxPassRate());
                r.createCell(25).setCellValue(s.getZjxFailCount() == null ? 0 : s.getZjxFailCount());
                r.createCell(26).setCellValue(s.getZjxFailRate() == null ? 0.0 : s.getZjxFailRate());

                // 总评 counts/rates (27-34)
                r.createCell(27).setCellValue(s.getZpExcellentCount() == null ? 0 : s.getZpExcellentCount());
                r.createCell(28).setCellValue(s.getZpExcellentRate() == null ? 0.0 : s.getZpExcellentRate());
                r.createCell(29).setCellValue(s.getZpGoodCount() == null ? 0 : s.getZpGoodCount());
                r.createCell(30).setCellValue(s.getZpGoodRate() == null ? 0.0 : s.getZpGoodRate());
                r.createCell(31).setCellValue(s.getZpPassCount() == null ? 0 : s.getZpPassCount());
                r.createCell(32).setCellValue(s.getZpPassRate() == null ? 0.0 : s.getZpPassRate());
                r.createCell(33).setCellValue(s.getZpFailCount() == null ? 0 : s.getZpFailCount());
                r.createCell(34).setCellValue(s.getZpFailRate() == null ? 0.0 : s.getZpFailRate());
            }

            // 6) Course Gap sheet
            Sheet cgSheet = workbook.createSheet("course-gap");
            Row cgHeader = cgSheet.createRow(0);
            // 不再记录具体队别名称，改为记录课程包含的队数（teamCount）并在每个指标处显示
            String[] cgCols = new String[]{"学期","课程代码","课程名称",
            "形成性最大平均差距","形成性-优秀率差","形成性-不及格率差","队数",
            "终结性最大平均差距","终结性-优秀率差","终结性-不及格率差","队数",
            "总评最大平均差距","总评-优秀率差","总评-不及格率差","队数"};
            for (int i = 0; i < cgCols.length; i++) cgHeader.createCell(i).setCellValue(cgCols[i]);

            List<org.lc.scoretj.model.CourseGapAnalysis> cgStats = xueQe != null && !xueQe.isEmpty()
                    ? courseGapAnalysisRepository.findByXueQe(xueQe, org.springframework.data.domain.Pageable.unpaged()).getContent()
                    : courseGapAnalysisRepository.findAll();

            int cgRowIdx = 1;
            for (org.lc.scoretj.model.CourseGapAnalysis s : cgStats) {
                Row r = cgSheet.createRow(cgRowIdx++);
                r.createCell(0).setCellValue(s.getXueQe() == null ? "" : s.getXueQe());
                r.createCell(1).setCellValue(s.getCourseCode() == null ? "" : s.getCourseCode());
                r.createCell(2).setCellValue(s.getCourseName() == null ? "" : s.getCourseName());
                // 形成性最大平均差及优秀/不及格率差，写入队数
                r.createCell(3).setCellValue(s.getXcxGapAverage() == null ? 0.0 : s.getXcxGapAverage());
                r.createCell(4).setCellValue(s.getXcxExcellentRateGap() == null ? 0.0 : s.getXcxExcellentRateGap());
                r.createCell(5).setCellValue(s.getXcxFailRateGap() == null ? 0.0 : s.getXcxFailRateGap());
                r.createCell(6).setCellValue(s.getTeamCount() == null ? 0 : s.getTeamCount());
                // 终结性最大平均差及优秀/不及格率差，写入队数
                r.createCell(7).setCellValue(s.getZjxGapAverage() == null ? 0.0 : s.getZjxGapAverage());
                r.createCell(8).setCellValue(s.getZjxExcellentRateGap() == null ? 0.0 : s.getZjxExcellentRateGap());
                r.createCell(9).setCellValue(s.getZjxFailRateGap() == null ? 0.0 : s.getZjxFailRateGap());
                r.createCell(10).setCellValue(s.getTeamCount() == null ? 0 : s.getTeamCount());
                // 总评最大平均差及优秀/不及格率差，写入队数
                r.createCell(11).setCellValue(s.getZpGapAverage() == null ? 0.0 : s.getZpGapAverage());
                r.createCell(12).setCellValue(s.getZpExcellentRateGap() == null ? 0.0 : s.getZpExcellentRateGap());
                r.createCell(13).setCellValue(s.getZpFailRateGap() == null ? 0.0 : s.getZpFailRateGap());
                r.createCell(14).setCellValue(s.getTeamCount() == null ? 0 : s.getTeamCount());
            }

            // Course correlation has been removed

            fileName = "statistics" + (xueQe != null ? ("-" + xueQe) : "") + ".xlsx";
            response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename*=UTF-8''" + URLEncoder.encode(fileName, "UTF-8"));
            response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
            ServletOutputStream out = response.getOutputStream();
            workbook.write(out);
            out.flush();
        } catch (Exception e) {
            logger.error("Failed to export Excel for xueQe={}; fileName={}", xueQe, fileName, e);
            throw new RuntimeException("导出Excel失败", e);
        }
    }

    /**
     * 导入成绩（Excel .xlsx），批量写入 score 表
     */
    @GetMapping("/import/template")
    public void downloadImportTemplate(HttpServletResponse response) {
        String[] headers = new String[]{"学期","学号","姓名","年级","培养层次","专业","队别","教学班","课程代码","课程名称","课程类型","考核类型","形成性","终结性","总评","教师"};
        try (XSSFWorkbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("template");
            Row header = sheet.createRow(0);
            for (int i = 0; i < headers.length; i++) header.createCell(i).setCellValue(headers[i]);

            String fileName = "score_import_template.xlsx";
            response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename*=UTF-8''" + URLEncoder.encode(fileName, "UTF-8"));
            response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
            ServletOutputStream out = response.getOutputStream();
            workbook.write(out);
            out.flush();
        } catch (Exception e) {
            logger.error("Failed to generate import template", e);
            throw new RuntimeException("导出模板失败", e);
        }
    }

    @PostMapping(value = "/import", consumes = {"multipart/form-data"})
    public ResponseEntity<ApiResponse<org.lc.scoretj.dto.ImportResultDTO>> importScores(@RequestPart("file") MultipartFile file) {
        if (file == null || file.isEmpty()) {
            return ResponseEntity.badRequest().body(ApiResponse.error("上传文件为空"));
        }

        List<org.lc.scoretj.dto.ImportFailureDTO> failures = new ArrayList<>();
        int totalRows = 0;
        int imported = 0;

        try (InputStream in = file.getInputStream(); XSSFWorkbook workbook = new XSSFWorkbook(in)) {
            Sheet sheet = workbook.getSheetAt(0);
            totalRows = Math.max(0, sheet.getLastRowNum());
            List<org.lc.scoretj.model.Score> batch = new ArrayList<>();

            for (int r = 1; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
                if (row == null) continue;
                try {
                    org.lc.scoretj.model.Score s = new org.lc.scoretj.model.Score();
                    s.setXueQe(getStringCell(row, 0));
                    s.setXh(getStringCell(row, 1));
                    s.setName(getStringCell(row, 2));
                    s.setGrade(getStringCell(row, 3));
                    s.setPeiYangCengCi(getStringCell(row, 4));
                    s.setZhuanYe(getStringCell(row, 5));
                    s.setDuiBie(getStringCell(row, 6));
                    s.setJiaoXueBan(getStringCell(row, 7));
                    s.setCourseCode(getStringCell(row, 8));
                    s.setCourseName(getStringCell(row, 9));
                    s.setCourseType(getStringCell(row, 10));
                    s.setExamType(getStringCell(row, 11));
                    s.setXcxScore(getFloatCell(row, 12));
                    s.setZjxScore(getFloatCell(row, 13));
                    s.setZpScore(getFloatCell(row, 14));
                    s.setTeacher(getStringCell(row, 15));

                    // 简单校验：学期和学号与课程代码不能为空
                    if (s.getXueQe() == null || s.getXueQe().isEmpty() || s.getXh() == null || s.getXh().isEmpty() || s.getCourseCode() == null || s.getCourseCode().isEmpty()) {
                        failures.add(new org.lc.scoretj.dto.ImportFailureDTO(r, "缺少必填字段（学期/学号/课程代码）"));
                        continue;
                    }

                    batch.add(s);
                    // 批量保存策略：每500条保存一次
                    if (batch.size() >= 500) {
                        scoreRepository.saveAll(batch);
                        imported += batch.size();
                        batch.clear();
                    }
                } catch (Exception ex) {
                    failures.add(new org.lc.scoretj.dto.ImportFailureDTO(r, ex.getMessage()));
                }
            }

            if (!batch.isEmpty()) {
                scoreRepository.saveAll(batch);
                imported += batch.size();
            }

            org.lc.scoretj.dto.ImportResultDTO result = new org.lc.scoretj.dto.ImportResultDTO(totalRows, imported, failures);
            return ResponseEntity.ok(ApiResponse.success("导入完成", result));
        } catch (Exception e) {
            logger.error("Import failed", e);
            return ResponseEntity.internalServerError().body(ApiResponse.error("导入失败: " + e.getMessage()));
        }
    }

    // 辅助方法读取单元格
    private static final DataFormatter DATA_FORMATTER = new DataFormatter();
    private static String getStringCell(Row row, int idx) {
        try {
            if (row.getCell(idx) == null) return null;
            String v = DATA_FORMATTER.formatCellValue(row.getCell(idx));
            return v == null ? null : v.trim();
        } catch (Exception e) {
            return null;
        }
    }

    private static Float getFloatCell(Row row, int idx) {
        try {
            if (row.getCell(idx) == null) return null;
            if (row.getCell(idx).getCellType() == org.apache.poi.ss.usermodel.CellType.NUMERIC) {
                return (float) row.getCell(idx).getNumericCellValue();
            } else {
                String s = row.getCell(idx).toString();
                if (s == null || s.trim().isEmpty()) return null;
                return Float.parseFloat(s.trim());
            }
        } catch (Exception e) {
            return null;
        }
    }
}